#！/bin/bash
source /etc/profile
if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
drop table if exists tmp.tmp_trade_shops;
create table tmp.tmp_trade_shops as
(
    select 
        dim.shopid, dim.userid, dim.areaid, dim.shopname, dim.shoplevel, dim.status, dim.createtime, dim.modifytime,
        dim.start_dt,
        case when ods.shopid is not null and dim.modifytime<=>ods.modifytime then dim.end_dt --当日没有变化的记录
        else '$do_date'                                                                      --删除的、当日变化的记录的旧纪录
        end as end_dt
    from dim.dim_trade_shops dim left join (select * from ods.ods_trade_shops where dt='$do_date') ods
    on dim.shopid=ods.shopid
    union all
    select
        ods.shopid, ods.userid, ods.areaid, ods.shopname, ods.shoplevel, ods.status, ods.createtime, ods.modifytime,
        case  when ods.modifytime is not null 
            then substr(ods.modifytime,1,10)
            else substr(ods.createtime,1,10) 
        end as start_dt,
        '9999-12-31' as end_dt                                                               --新增的、当日变化记录的新记录
    from (select * from ods.ods_trade_shops where dt='$do_date') ods left join (select * from dim.dim_trade_shops where end_dt>='9999-12-31')dim
    on dim.shopid=ods.shopid
    where dim.shopid is null or (not (ods.modifytime <=> dim.modifytime ))
);
insert overwrite table dim.dim_trade_shops select * from tmp.tmp_trade_shops;
" 
hive -e "$sql"